var express = require('express');
var router = express.Router();

let connection = require('../base/ConnLocal').getConn();

/**
 * 数据库连接信息
 */
router.get('/dbinfo', function (req, res, next) {
  const config = connection.config;
  const retObj = {
    "host": config.host,
    "port": config.port,
    "database": config.database,
  };
  res.send(retObj);
});

/**
 * 所有表格名称
 */
router.get('/list', function (req, res, next) {
  const query = req.query;
  const sql = `SELECT * FROM report_boot.mate_parser WHERE 1 = 1`;
  connection.query(sql, function (error, results, fields) {
    if (error) {
      console.log(error);
      res.send({
        code: 500,
        msg: error.sqlMessage
      });
      return;
    };
    console.log(sql, results.length);

    // const retList = results.map(item => {
    //   return item['TABLE_NAME'];
    // })
    res.send(results);
  });
});

/**
 * 单个表格结构
 */
router.get('/desc/:tableName', function (req, res, next) {
  let tableName = req.params.tableName;
  if (!tableName || tableName === 'null') {
    console.log('-----tableName is null------');
    res.send([]);
    return;
  }

  const sql = `SELECT * FROM information_schema.columns WHERE table_name = '${tableName}'`;
  connection.query(sql, function (error, results, fields) {
    if (error) {
      console.log(error);
      res.send({
        code: 500,
        msg: error.sqlMessage
      });
      return;
    };
    console.log(sql, results.length);
    res.send(results);
  });
});

/**
 * 单个表格数据
 */
router.get('/query/:tableName', function (req, res, next) {
  let tableName = req.params.tableName;
  if (!tableName || tableName === 'null') {
    console.log('-----tableName is null------');
    res.send([]);
    return;
  }

  let whereClause = " where 1 = 1";
  for (let key in req.query) {
    whereClause += " and " + key + " like '%" + req.query[key] + "%'";
  }

  whereClause += ' limit 0, 100';

  connection.query('SELECT * from ' + tableName + whereClause, function (error, results, fields) {
    if (error) {
      console.log(error);
      res.send({
        code: 500,
        msg: error.sqlMessage
      });
      return;
    };
    console.log('query table ' + tableName + ', length: ', results.length);

    res.send(results);
  });
});

/**
 * 单个表格总数
 */
router.get('/total/:tableName', function (req, res, next) {
  let tableName = req.params.tableName;
  if (!tableName || tableName === 'null') {
    console.log('-----tableName is null------');
    res.send([]);
    return;
  }

  let whereClause = " where 1 = 1";
  for (let key in req.query) {
    whereClause += " and " + key + " like '%" + req.query[key] + "%'";
  }

  connection.query('SELECT count(*) as total from ' + tableName + whereClause, function (error, results, fields) {
    if (error) {
      console.log(error);
      res.send({
        code: 500,
        msg: error.sqlMessage
      });
      return;
    };
    console.log('query table ' + tableName + ', length: ', results.length);

    res.send(results);
  });
});

module.exports = router;
